﻿using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace MCPSSETest.Utils
{
    public static class ExcelHelper
    {

        public static JArray GetExcelJArray(string filePath)
        {
            if (string.IsNullOrEmpty(filePath))
                throw new ArgumentException("文件路径不能为空", nameof(filePath));

            if (!File.Exists(filePath))
                throw new FileNotFoundException($"文件不存在: {filePath}");

            using (var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;

                // 根据文件扩展名创建相应的Workbook
                if (filePath.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
                {
                    workbook = new XSSFWorkbook(fileStream);
                }
                else if (filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase))
                {
                    workbook = new HSSFWorkbook(fileStream);
                }
                else
                {
                    throw new ArgumentException("不支持的文件格式，仅支持.xls和.xlsx文件");
                }

                // 获取第一个工作表
                ISheet sheet = workbook.GetSheetAt(0);
                return ProcessSheet(sheet);
            }
        }
        private static JArray ProcessSheet(ISheet sheet)
        {
            var jArray = new JArray();

            if (sheet == null || sheet.LastRowNum < 1)
                return jArray;

            // 读取表头（第一行）
            IRow headerRow = sheet.GetRow(0);
            if (headerRow == null)
                return jArray;

            var headers = new List<string>();
            int colCount = headerRow.LastCellNum;

            // 读取表头单元格
            for (int col = 0; col < colCount; col++)
            {
                ICell cell = headerRow.GetCell(col);
                string headerName = GetCellValue(cell)?.ToString()?.Trim();
                headers.Add(string.IsNullOrEmpty(headerName) ? $"Column{col + 1}" : headerName);
            }

            // 读取数据行（从第二行开始）
            for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                IRow dataRow = sheet.GetRow(rowIndex);
                if (dataRow == null) continue;

                var jObject = new JObject();
                bool hasData = false;

                for (int col = 0; col < colCount; col++)
                {
                    if (col >= headers.Count) break;

                    ICell cell = dataRow.GetCell(col);
                    object cellValue = GetCellValue(cell);

                    if (cellValue != null)
                    {
                        hasData = true;
                        jObject[headers[col]] = ConvertToJToken(cellValue);
                    }
                }

                if (hasData)
                    jArray.Add(jObject);
            }

            return jArray;
        }
        private static object GetCellValue(ICell cell)
        {
            if (cell == null)
                return null;

            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;

                case CellType.Numeric:
                    // 检查是否是日期格式
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue;
                    }
                    return cell.NumericCellValue;

                case CellType.Boolean:
                    return cell.BooleanCellValue;

                case CellType.Formula:
                    // 处理公式单元格，获取计算公式的值
                    try
                    {
                        switch (cell.CachedFormulaResultType)
                        {
                            case CellType.String:
                                return cell.StringCellValue;
                            case CellType.Numeric:
                                return cell.NumericCellValue;
                            case CellType.Boolean:
                                return cell.BooleanCellValue;
                            default:
                                return cell.ToString();
                        }
                    }
                    catch
                    {
                        return cell.ToString();
                    }

                case CellType.Blank:
                    return null;

                default:
                    return cell.ToString();
            }
        }
        private static JToken ConvertToJToken(object value)
        {
            if (value == null)
                return JValue.CreateNull();

            switch (value)
            {
                case string str:
                    return new JValue(str);
                case int intValue:
                    return new JValue(intValue);
                case double doubleValue:
                    return new JValue(doubleValue);
                case decimal decimalValue:
                    return new JValue((double)decimalValue); // NPOI返回的是double
                case float floatValue:
                    return new JValue(floatValue);
                case bool boolValue:
                    return new JValue(boolValue);
                case DateTime dateTimeValue:
                    return new JValue(dateTimeValue);
                case long longValue:
                    return new JValue(longValue);
                case short shortValue:
                    return new JValue(shortValue);
                default:
                    return new JValue(value.ToString());
            }
        }


        public static void WriteJArrayToExcel(JArray jArray, string filePath, string sheetName = "Sheet1")
        {
            if (jArray == null)
                throw new ArgumentNullException(nameof(jArray), "JArray不能为null");

            if (string.IsNullOrEmpty(filePath))
                throw new ArgumentException("文件路径不能为空", nameof(filePath));

            IWorkbook workbook;
            bool fileExists = File.Exists(filePath);

            // 如果文件已存在，先读取现有文件
            if (fileExists)
            {
                using (var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = filePath.EndsWith(".xlsx") ?
                        new XSSFWorkbook(fileStream) : new HSSFWorkbook(fileStream);
                }
            }
            else
            {
                // 创建新工作簿，根据文件扩展名决定格式
                workbook = filePath.EndsWith(".xlsx") ?
                    new XSSFWorkbook() : new HSSFWorkbook();
            }

            // 处理工作表
            ISheet sheet = workbook.GetSheet(sheetName);
            if (sheet != null)
            {
                // 如果工作表已存在，移除它
                int sheetIndex = workbook.GetSheetIndex(sheet);
                workbook.RemoveSheetAt(sheetIndex);
            }

            // 创建新工作表
            sheet = workbook.CreateSheet(sheetName);

            // 写入数据
            WriteDataToSheet(jArray, sheet);

            // 保存文件
            using (var fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fileStream);
            }
        }

        private static void WriteDataToSheet(JArray jArray, ISheet sheet)
        {
            if (jArray.Count == 0)
                return;

            // 获取所有属性名（表头）
            var headers = new HashSet<string>();
            foreach (JObject item in jArray)
            {
                foreach (var property in item.Properties())
                {
                    headers.Add(property.Name);
                }
            }

            var headerList = new List<string>(headers);
            int colCount = headerList.Count;

            // 创建表头行
            IRow headerRow = sheet.CreateRow(0);
            for (int col = 0; col < colCount; col++)
            {
                ICell cell = headerRow.CreateCell(col);
                cell.SetCellValue(headerList[col]);

                // 设置表头样式
                var headerStyle = sheet.Workbook.CreateCellStyle();
                var headerFont = sheet.Workbook.CreateFont();
                headerFont.IsBold = true;
                headerStyle.SetFont(headerFont);
                headerStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
                headerStyle.FillPattern = FillPattern.SolidForeground;
                cell.CellStyle = headerStyle;
            }

            // 写入数据行
            for (int row = 0; row < jArray.Count; row++)
            {
                JObject item = jArray[row] as JObject;
                if (item == null) continue;

                IRow dataRow = sheet.CreateRow(row + 1);

                for (int col = 0; col < colCount; col++)
                {
                    string header = headerList[col];
                    ICell cell = dataRow.CreateCell(col);

                    if (item.TryGetValue(header, out JToken value))
                    {
                        SetCellValue(cell, value);
                    }
                }
            }

            // 自动调整列宽
            for (int col = 0; col < colCount; col++)
            {
                sheet.AutoSizeColumn(col);
            }
        }

        private static void SetCellValue(ICell cell, JToken value)
        {
            if (value == null || value.Type == JTokenType.Null)
            {
                cell.SetCellValue((string)null);
                return;
            }

            switch (value.Type)
            {
                case JTokenType.String:
                    cell.SetCellValue(value.Value<string>());
                    break;

                case JTokenType.Integer:
                    cell.SetCellValue(value.Value<long>());
                    break;

                case JTokenType.Float:
                    cell.SetCellValue(value.Value<double>());
                    break;

                case JTokenType.Boolean:
                    cell.SetCellValue(value.Value<bool>());
                    break;

                case JTokenType.Date:
                    cell.SetCellValue(value.Value<DateTime>());

                    // 设置日期格式
                    var dateStyle = cell.Sheet.Workbook.CreateCellStyle();
                    var format = cell.Sheet.Workbook.CreateDataFormat();
                    dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
                    cell.CellStyle = dateStyle;
                    break;

                case JTokenType.Object:
                case JTokenType.Array:
                    // 复杂对象转换为JSON字符串
                    cell.SetCellValue(value.ToString());
                    break;

                default:
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }
    }
}
